package com.elm.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.elm.dao.SellerDao;
import com.elm.model.Role;
import com.elm.model.Seller;
import com.elm.model.User;
import com.elm.util.JDBCUtil;

public class SellerDaoImpl implements SellerDao{

	@Override
	public Seller validateUser(Seller s) {
		boolean  flag = false;
		Seller seller = new Seller();
		PreparedStatement pst = null;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from seller where (sellername=? or sellerphone =?) and sellerpsw =?");
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, s.getSellername());
			pst.setString(2, s.getSellername());
			pst.setString(3, s.getSellerpsw());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				seller.setSellerid(rs.getInt("sellerid"));
				seller.setSellername(rs.getString("sellername"));
				seller.setSellerpsw(rs.getString("sellerpsw"));
				seller.setSellerphone(rs.getString("sellerphone"));
				seller.setSelleraddr(rs.getString("selleraddr"));
				seller.setShopgenre(rs.getString("shopgenre"));
				seller.setShopname(rs.getString("shopname"));
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return seller;
	}

	@Override
	public Role getRoleBySid(int Sid) {
		// TODO Auto-generated method stub
		Role role = new Role();
		PreparedStatement pst = null;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("SELECT role.roleid,rolename,rolefuncs  from role,seller where seller.sellerid=? and seller.roleid = role.roleid");
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setInt(1, Sid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				role.setRoleid(rs.getInt("roleid"));
				role.setRolename(rs.getString("rolename"));
				role.setFuncs(rs.getString("rolefuncs"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return role;
	}
/**
 * 通过商家名查找商家
 */
	@Override
	public List<Seller> FindSellerByName(int start, int pageSize, String searchname) {
		// TODO Auto-generated method stub
		List<Seller> sr = new ArrayList<Seller>();
		Connection con  = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select sellerid,sellername,shopname,sellerphone,selleraddr,shopgenre from seller where sellername like ? and allowed='1'");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Seller s = new Seller();
				s.setSellerid(rs.getInt("sellerid"));
				s.setSellername(rs.getString("sellername"));
				s.setSellerphone(rs.getString("sellerphone"));
				s.setSelleraddr(rs.getString("selleraddr"));
				s.setShopgenre(rs.getString("shopgenre"));
				sr.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sr;
	}
/**
 * 模糊查询条件下共有几条数据
 */
	@Override
	public int getSellersCountByName(String searchname) {
		// TODO Auto-generated method stub
		int srcount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(sellerid) srcount from seller where sellername like ? and allowed='1'");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchname+"%");
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				srcount = rs.getInt("srcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return srcount;
	}
/**
 * 查询所有商家信息
 */
	@Override
	public List<Seller> FindAllSellers(int start, int pageSize) {
		// TODO Auto-generated method stub
		List<Seller> allsr =new ArrayList<Seller>();
		Connection con  = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select sellerid,sellername,shopname,sellerphone,selleraddr,shopgenre from seller where allowed=1 and isdel=0");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Seller sr = new Seller();
				sr.setSellerid(rs.getInt("sellerid"));
				sr.setSellername(rs.getString("sellername"));
				sr.setSellerphone(rs.getString("sellerphone"));
				sr.setSelleraddr(rs.getString("selleraddr"));
				sr.setShopgenre(rs.getString("shopgenre"));
				allsr.add(sr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return allsr;
	}
/**
 * 查询总共有多少商家
 */
	@Override
	public int getSellersCount() {
		// TODO Auto-generated method stub
		int srcount  = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(sellerid) srcount from seller where allowed=1 and isdel=0");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
		if(rs.next()){
			srcount  = rs.getInt("srcount");
		}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return srcount;
	}
/**
 * 删除商家信息
 */
	@Override
	public boolean deleteseller(int sellerid) {
		// TODO Auto-generated method stub
		Connection con  = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update seller set isdel=1 where sellerid=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sellerid);
			int j = pst.executeUpdate();
			if(j>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

/**
 * 查询所有的申请信息
 * @param start
 * @param pageSize
 * @return
 */
public List<Seller> FindAllApplySellers(int start, int pageSize) {
	List<Seller> applysrs =new ArrayList<Seller>();
	Connection con  = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("select sellerid,sellername,shopname,sellerphone,selleraddr,shopgenre,allowed from seller where allowed=0 and roleid=2");
	sql.append(" limit ?,?");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		pst.setInt(1, start);
		pst.setInt(2, pageSize);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Seller sr = new Seller();
			sr.setSellerid(rs.getInt("sellerid"));
			sr.setSellername(rs.getString("sellername"));
			sr.setShopname(rs.getString("shopname"));
			sr.setSellerphone(rs.getString("sellerphone"));
			sr.setSelleraddr(rs.getString("selleraddr"));
			sr.setShopgenre(rs.getString("shopgenre"));
			sr.setAllowed(rs.getInt("allowed"));
			applysrs.add(sr);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return applysrs;
}
/**
 * 查询总共有多少申请信息
 * @return
 */
public int getApplySellersCount() {
	int applysrscount  = 0;
	Connection con = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("select count(sellerid) applysrscount from seller where allowed=0 and roleid=2");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		ResultSet rs = pst.executeQuery();
	if(rs.next()){
		applysrscount  = rs.getInt("applysrscount");
	}	
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return applysrscount;
}

/**
 * 通过名字查询申请信息
 * @param start
 * @param pageSize
 * @param searchname
 * @return
 */
public List<Seller> FindApplySellerByName(int start, int pageSize, String searchname) {
	List<Seller> applysr = new ArrayList<Seller>();
	Connection con  = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("select sellerid,sellername,shopname,sellerphone,selleraddr,shopgenre,allowed from seller where sellername like ? and allowed=0 and roleid=2");
	sql.append(" limit ?,?");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		pst.setString(1, "%"+searchname+"%");
		pst.setInt(2, start);
		pst.setInt(3, pageSize);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Seller s = new Seller();
			s.setSellerid(rs.getInt("sellerid"));
			s.setSellername(rs.getString("sellername"));
			s.setSellerphone(rs.getString("sellerphone"));
			s.setSelleraddr(rs.getString("selleraddr"));
			s.setShopgenre(rs.getString("shopgenre"));
			s.setAllowed(rs.getInt("allowed"));
			applysr.add(s);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return applysr;
}

/**
 * 模糊查询条件下有多少申请
 * @param searchname
 * @return
 */
public int getApplySellerCountByName(String searchname) {
	int applysrcount = 0;
	Connection con = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("select count(sellerid) applysrcount from seller where sellername like ? and allowed='0'");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		pst.setString(1, "%"+searchname+"%");
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			applysrcount = rs.getInt("applysrcount");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return applysrcount;
}

/**
 * 通过id来通过开店申请
 * @param sellerid
 * @return
 */
public boolean passseller(int sellerid) {
	Connection con  = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("update seller set allowed=1 where sellerid=? and isdel=0");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		pst.setInt(1, sellerid);
		int j = pst.executeUpdate();
		if(j>0){
			return true;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}

/**
 * 查询全部过往商家
 */

public List<Seller> FindOldSellers(int start, int pageSize) {
	List<Seller> alloldsr =new ArrayList<Seller>();
	Connection con  = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("select sellerid,sellername,shopname,sellerphone,selleraddr,shopgenre,isdel from seller where allowed=1 and roleid=2 and isdel=1");
	sql.append(" limit ?,?");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		pst.setInt(1, start);
		pst.setInt(2, pageSize);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Seller sr = new Seller();
			sr.setSellerid(rs.getInt("sellerid"));
			sr.setSellername(rs.getString("sellername"));
			sr.setShopname(rs.getString("shopname"));
			sr.setSellerphone(rs.getString("sellerphone"));
			sr.setSelleraddr(rs.getString("selleraddr"));
			sr.setShopgenre(rs.getString("shopgenre"));
			sr.setIsdel(rs.getInt("isdel"));
			alloldsr.add(sr);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return alloldsr;
}
/**
 * 总共有多少过往商家
 */
public int getOldSellersCount() {
	int alloldsrscount  = 0;
	Connection con = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("select count(sellerid) alloldsrscount from seller where allowed=1 and roleid=2 and isdel=1");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		ResultSet rs = pst.executeQuery();
	if(rs.next()){
		alloldsrscount  = rs.getInt("alloldsrscount");
	}	
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return alloldsrscount;
}

/**
 * 通过名字来查找过往商家
 */


public List<Seller> FindOldSellerByName(int start, int pageSize, String searchname) {
	List<Seller> oldsr = new ArrayList<Seller>();
	Connection con  = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("select sellerid,sellername,shopname,sellerphone,selleraddr,shopgenre,isdel from seller where sellername like ? and allowed=1 and roleid=2 and isdel=1");
	sql.append(" limit ?,?");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		pst.setString(1, "%"+searchname+"%");
		pst.setInt(2, start);
		pst.setInt(3, pageSize);
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			Seller s = new Seller();
			s.setSellerid(rs.getInt("sellerid"));
			s.setSellername(rs.getString("sellername"));
			s.setShopname(rs.getString("shopname"));
			s.setSellerphone(rs.getString("sellerphone"));
			s.setSelleraddr(rs.getString("selleraddr"));
			s.setShopgenre(rs.getString("shopgenre"));
			s.setAllowed(rs.getInt("isdel"));
			oldsr.add(s);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return oldsr;
}

/**
 * 模糊查询下有多少过往商家
 */
public int getOldSellerCountByName(String searchname) {
	int oldsrcount = 0;
	Connection con = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("select count(sellerid) oldsrcount from seller where sellername like ? and allowed=1 and roleid=2 and isdel=1");
	try {
		PreparedStatement pst = con.prepareStatement(sql.toString());
		pst.setString(1, "%"+searchname+"%");
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			oldsrcount = rs.getInt("oldsrcount");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return oldsrcount;
}


/**
 * 通过id来还原商家
 */

public boolean restoreseller(int seid) {
	Connection con = JDBCUtil.getConnection();
	StringBuffer sql = new StringBuffer("update seller set isdel=0 where sellerid=?");
	try {
		PreparedStatement pst =con.prepareStatement(sql.toString());
		pst.setInt(1, seid);
		int j = pst.executeUpdate();
		while(j>0){
			return true;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}






	
}
